# -*- coding:utf-8 -*-
"""

@author: liandyao
@date: 2025/1/11 12:50
"""
import pandas as pd
import pymysql
from pymysql import MySQLError

# 数据库配置
db_config = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': '123',
    'database': 'mymusic'
}

# 读取Excel文件
excel_file_path = '情感故事.xlsx'  # 替换为你的Excel文件路径
df = pd.read_excel(excel_file_path)

# 连接到MySQL数据库
try:
    # 连接到 MySQL
    connection = pymysql.connect(**db_config)

    if connection.open:
        print("成功连接到MySQL数据库")
        cursor = connection.cursor()

        # 插入数据到MySQL表中
        for index, row in df.iterrows():
            query = """
            INSERT INTO dati_love_story (tags, title, content, status, remark)
            VALUES (%s, %s, %s, %s, %s)
            """
            values = (row['分类'], row['标题'], row['内容'], None, None)  # 假设status和remark字段为空
            cursor.execute(query, values)

        # 提交事务
        connection.commit()
        print(f"成功插入 {cursor.rowcount} 条记录")

except MySQLError as e:
    print(f"Error: {e}")

finally:
    if connection.open:
        cursor.close()
        connection.close()
        print("MySQL连接已关闭")
